
*Getting Dataset In Order
*Adhikari, King, Murdie ISA 2023

*Country Year dataset, merged on COW code 

clear 
cd "C:\Users\amand\Dropbox\2022 Adhikari King and Murdie\Democratic Backsliding Paper\Data Analysis"

*QoG data - updated with QoG big on 2/26/2023
	use "qog_std_ts_jan23_stata14", clear
	drop ccode
	rename ccodecow ccode
	duplicates tag ccode year, gen(QoGdup)
	sort ccode  year
	drop if ccode==.
	tsset ccode year
	drop QoGdup
	save QoGccode.dta, replace

*VDEM Data
	use "Country_Year_V-Dem_Core_STATA_v12\V-Dem-CY-Core-v12.dta", clear
	rename COWcode ccode
	duplicates tag ccode year, gen(VDEMdup)
	tab country_name if VDEMdup>=1
	drop if VDEMdup>=1
	drop VDEMdup
	tsset ccode year
	save VDEM.dta, replace

*Fariss
	import delimited "HumanRightsProtectionScores_v4.01.csv", clear 
	rename cow ccode
	tsset ccode year
	keep theta_mean-killing_estimate_medi ccode year amnesty state hathaway itt genocide rummel massive_repression
	save HRscores.dta, replace	

*Conflict (then merge with QoG and fill)
		use "ucdp-prio-acd-221.dta", clear
		move  gwno_loc location
		move year location
		sort gwno_loc year
		split gwno_loc , p(",")

		move gwno_loc1 year
		move gwno_loc2 year
		move gwno_loc3 year
		move gwno_loc4 year
		move gwno_loc5 year
		move gwno_loc6 year
		sort gwno_loc2 year

		destring gwno_loc6, replace
		destring gwno_loc5, replace
		destring gwno_loc4, replace
		destring gwno_loc3, replace
		destring gwno_loc2, replace
		destring gwno_loc1, replace

		expand 6 if gwno_loc6!=.
		expand 5 if gwno_loc5!=. & gwno_loc6==.
		expand 4 if gwno_loc4!=. & gwno_loc6==. & gwno_loc5==.
		expand 3 if gwno_loc3!=. & gwno_loc6==. & gwno_loc5==. & gwno_loc4==. 
		expand 2 if gwno_loc2!=. & gwno_loc6==. & gwno_loc5==. & gwno_loc4==. &  gwno_loc3==. 


		sort conflict_id year

		duplicates tag gwno_loc conflict year, gen(duplicates)
		tab duplicates
		*0,1,2,4,5

		gen ccode = gwno_loc1 if  gwno_loc2==.
		move ccode gwno_loc 
		move duplicates ccode  

		replace ccode=gwno_loc1 if duplicates==1 & ccode==. & year!=year[_n-1]
		replace ccode=gwno_loc1 if duplicates==1 & ccode==. & gwno_loc!=gwno_loc[_n-1]
		replace ccode=gwno_loc2 if ccode==. & duplicates==1 



		replace ccode=gwno_loc1 if duplicates==2 & ccode==. & year!=year[_n-1]
		replace ccode=gwno_loc2 if ccode==. & duplicates==2 & year==year[_n+1] & year==year[_n-1] 
		replace ccode=gwno_loc3 if ccode==. & duplicates==2 & year!=year[_n+1] & year==year[_n-1] 


		replace ccode=gwno_loc1 if duplicates==3 & ccode==. & year!=year[_n-1]
		replace ccode=gwno_loc2 if ccode==. & duplicates==3 & year==year[_n+1] & year==year[_n-1]  & year==year[_n+2]
		replace ccode=gwno_loc3 if ccode==. & duplicates==3 & year==year[_n+1] & year==year[_n-1]  & year==year[_n-2]
		replace ccode=gwno_loc4 if ccode==. & duplicates==3 


		replace ccode=gwno_loc1 if duplicates==3 & ccode==. & year!=year[_n-1]
		replace ccode=gwno_loc2 if ccode==. & duplicates==3 & year==year[_n+1] & year==year[_n-1]  & year==year[_n+2]
		replace ccode=gwno_loc3 if ccode==. & duplicates==3 & year==year[_n+1] & year==year[_n-1]  & year==year[_n-2]
		replace ccode=gwno_loc4 if ccode==. & duplicates==3 


		browse if duplicates==5
		replace ccode=gwno_loc1 if duplicates==5 & ccode==. & year!=year[_n-1]
		replace ccode=gwno_loc2 if ccode==. & duplicates==5 & year==year[_n+1] & year==year[_n-1]  & year==year[_n+2]  & year==year[_n+3] & year==year[_n+4]
		replace ccode=gwno_loc3 if ccode==. & duplicates==5 & year==year[_n+1] & year==year[_n+2]  & year==year[_n+3] & year==year[_n-1]  & year==year[_n-2] 
		replace ccode=gwno_loc4 if ccode==. & duplicates==5 & year==year[_n+1] & year==year[_n+2] 
		replace ccode=gwno_loc5 if ccode==. & duplicates==5 & year==year[_n+1]
		replace ccode=gwno_loc6 if ccode==. & duplicates==5 

		tab type, gen(Type)
		destring intensity, replace
		destring cum, replace 
		destring type, replace 
		destring year, replace
		collapse (max) intensity cumulative type Type1 Type2 Type3 Type4, by( ccode year)
		gen locationconflict=1
		tsset ccode year
		save UCDPlocation.dta, replace 		



		merge 1:1 ccode year using QoGccode.dta, nogen


		replace intensity=0 if intensity==. & year>=1946 & year<=2020
		replace cumulative=0 if cumulative==. & year>=1946 & year<=2020
		replace locationconflict=0 if locationconflict==. & year>=1946 & year<=2020
		replace type=0 if type==. & year>=1946 & year<=2020
		replace Type1=0 if Type1==. & year>=1946 & year<=2020
		replace Type2=0 if Type2==. & year>=1946 & year<=2020
		replace Type3=0 if Type3==. & year>=1946 & year<=2020
		replace Type4=0 if Type4==. & year>=1946 & year<=2020

		gen civillocation = 1 if Type3==1 | Type4==1
		replace civillocation = 0 if Type3==0 & Type4==0
		gen intllocation = 1 if Type1==1 |Type2==1
		replace intllocation=0 if Type1==0 & Type2==0	

		save QoGandconflict.dta, replace

*PTS Dataset 
	use "PTS-2022.dta", clear
	rename COW_Code_N ccode 
	rename Year year
	duplicates tag ccode year, gen(tag)
	tab Country if tag>0
	*Soviet Union pre 1992
	replace ccode=365 if Country_OLD=="Soviet Union" & year<1992
	*Yugoslavia - 1976 to 1992 
	replace ccode=345 if Country_OLD=="Yugoslavia" & year<1992
	*"Yugoslavia/Serbia and Montenegro" - 1991 to 2006
	replace ccode=345 if Country_OLD=="Yugoslavia/Serbia and Montenegro" & year>1991 & year<2007
	drop if Country_OLD=="Yugoslavia/Serbia and Montenegro" & year>1991 & year<2007
	drop if Country_OLD=="Yugoslavia" & year<1992
	drop if Country_OLD=="Russia" & year<1992
	drop if ccode==345 & year>2006
	drop tag
	drop if ccode==.
	duplicates tag ccode year, gen(tag)
	save PTS.dta, replace

	
*Polity Dataset 
	import excel "p5v2018.xls", sheet("p5v2018") firstrow clear
	tsset ccode year 
	generate autocracy =1 if polity2>=-10 & polity2<=-6
	generate anocracy = 1 if polity2>=-5 & polity2<=5
	generate democracy=1 if polity2>=6 & polity2<=10
	tsset ccode year
	ssc inst tsspell 
	tsspell, cond(autocracy == 1)
	rename _seq autocracyduration
	rename _end autocracyend
	drop _spell 
	tsspell, cond(anocracy==1)
	rename _seq  anocracyduration
	rename _end anocracyend
	drop _spell
	tsspell, cond(democracy==1)
	rename _seq democracyduration
	rename _end democracyend
	drop _spell
	generate regimetypeduration = autocracyduration 
	replace regimetypeduration = anocracyduration if autocracyduration==0 & democracyduration==0
	replace regimetypeduration = democracyduration if autocracyduration==0 & anocracyduration==0
	save Polity.dta, replace

*adding new data on 2/26/2023
***Judicial Independence
import delimited "Latent Judicial Independence\LJI-estimates-20181210.csv", clear
tsset ccode year 
save JudicialIndependence.dta, replace

*CIRIGHTS 
*Cingranelli's new CIRIGHTS dataset 
import delimited "CIRIGHTS\CIRIGHTS_2021.01.21.csv", clear
drop ccode
rename cow ccode
tsset ccode year 
save CIRIGHTS.dta, replace 


*Human Rights aid dataset 
		import delimited "AidDataCoreThin_ResearchRelease_Level1_v3.1.csv", clear 
		keep if coalesced_purpose_code==15160
		gen AidHumanRights = commitment_amount_usd_constant if coalesced_purpose_code==15160
		collapse (sum) Aid*, by (recipient year)
		kountry recipient, from(other) stuck
		rename _ISO iso
		kountry iso, from(iso3n) to(cown)
		rename _COWN ccode
		tab recipient if ccode==.
		replace ccode=347 if recipient=="Kosovo"
		replace ccode=626 if recipient=="South Sudan"
		drop if ccode==.
		collapse (sum) Aid*, by (ccode year)
		tsset ccode year
		save "HRaid.dta", replace


**NGO aid dataset
		import delimited "AidDataCoreThin_ResearchRelease_Level1_v3.1.csv", clear 
		keep if coalesced_purpose_code==92010 | coalesced_purpose_code==92020 | coalesced_purpose_code==92030
		gen AidNationalNGOs = commitment_amount_usd_constant if coalesced_purpose_code==92010
		gen AidLocalRegionalNGOs = commitment_amount_usd_constant if coalesced_purpose_code==92020
		gen AidInternationalNGOs = commitment_amount_usd_constant if coalesced_purpose_code==92030
		gen AidAllNGOs = commitment_amount_usd_constant 
		collapse (sum) Aid*, by (recipient year)
		kountry recipient, from(other) stuck
		rename _ISO iso
		kountry iso, from(iso3n) to(cown)
		rename _COWN ccode
		tab recipient if ccode==.
		replace ccode=347 if recipient=="Kosovo"
		replace ccode=626 if recipient=="South Sudan"
		drop if ccode==.
		collapse (sum) Aid*, by (ccode year)
		tsset ccode year
		save "NGOaid.dta", replace

		
*general aid data & US aid
	import delimited "AidDataCoreDonorRecipientYear_ResearchRelease_Level1_v3.1.csv", clear 
	rename commitment_amount_usd_constant AllAid
	gen USAid = AllAid if donor=="United States"
	collapse (sum) AllAid USAid, by (recipient year)
	kountry recipient, from(other) stuck
	rename _ISO iso
	kountry iso, from(iso3n) to(cown)
	rename _COWN ccode
	tab recipient if ccode==.
	replace ccode=347 if recipient=="Kosovo"
	replace ccode=626 if recipient=="South Sudan"
	drop if ccode==.
	collapse (sum) AllAid USAid, by (ccode year)
	tsset ccode year
	save "Allaid.dta", replace


*Merging All Datasets together 
use "VDEM.dta", clear  
merge 1:1 ccode year using "HRscores.dta", nolabel nogen
merge 1:1 ccode year using "Polity.dta", nolabel nogen
merge 1:1 ccode year using "PTS.dta", nolabel nogen
merge 1:1 ccode year using "QoGandconflict.dta", nolabel nogen
merge 1:1 ccode year using "QoGandconflict.dta", nolabel nogen
merge 1:1 ccode year using "Allaid.dta", nolabel nogen
merge 1:1 ccode year using "HRaid.dta", nolabel nogen
merge 1:1 ccode year using "NGOaid.dta", nolabel nogen
merge 1:1 ccode year using "CIRIGHTS.dta", nolabel nogen  
merge 1:1 ccode year using "JudicialIndependence.dta", nolabel nogen 

*Restricting to 1945+
drop if year<=1945
tsset ccode year
sort ccode year
save WorkingDataset.dta, replace 


*Cleaning and creating new variables 


*fill 0s in Aid data (1947-2013) and missing observations for NGO TSMO dataset, logged versions of each

foreach var of varlist AllAid-AidAllNGOs {
	replace `var'=0 if `var'==. & year>=1947 & year<=2013
}	
*logged versions 

foreach var of varlist AllAid-AidAllNGOs wdi_pop wdi_gdpcapcon2015 {
	gen ln`var'=ln(`var'+1)
}

save WorkingDataset.dta, replace 
